# coding=utf-8

# input：
# 1.beta问题列表excel：问题链接+版本+SN+问题时间
# 2.9类手机apr问题列表excel：SN+问题时间+问题类型

# excel
import xlrd
from xlutils.copy import copy
import xlwt
# time
import datetime

# Beta问题Excel文件名（或路径）
path_beta = "beta.xls"

# Beta问题的版本，该版本APR问题导出Excel文件名（或路径）
apr_list = [
    ["mate10 41", "mate10 41.xls"],
    ["mate10_42", "mate10_42.xls"],
    ["mate10_pro_42", "mate10_pro.xls"],
    ["mate_rs 42", "mate_rs.xls"],
    ["p20 42", "p20 42.xls"],
    ["p20_pro 42", "p20_pro.xls"],
    ["honor10 42", "honor10.xls"],
    ["honor_v10 42", "honor_v10.xls"],
    [ "v9_play 42", "v9_play.xls"],
]
len_apr_list = len(apr_list)
rexcel_apr = [None] * len_apr_list
rsheet1_apr = [None] * len_apr_list

style = xlwt.easyxf('align: wrap on; font: color-index blue')


def get_version_index(version):
    for i in range(len_apr_list):
        if apr_list[i][0] == version:
            return i
        
    return -1


def search_by_sn_time(rsheet, sn, beta_time, wsheet, i):
    #     p("sn=" + sn + " beta_time=" + str(beta_time))
    
    result = None
    for row_no in range(1, rsheet.nrows):
        if rsheet.cell_value(row_no, 0) != sn:  # 0列是SN
            continue
        
        #         p("sn ok " + sn)
        apr_time = datetime.datetime.strptime(rsheet.cell_value(row_no, 1)[0:14], "%Y%m%d%H%M%S")  # 1列是时间
        #         p("apr_time=" + str(apr_time))
    
        delta1 = (apr_time - beta_time).seconds
        delta2 = (beta_time - apr_time).seconds
        if (delta1 <= 60 * 10) or (delta2 <= 60 * 10):  # 10分钟
            if result == None:
                result = str(apr_time) + ": " + rsheet.cell_value(row_no, 2)  # 2列是问题类型
            else:
                result = result + "\n" + str(apr_time) + ": " + rsheet.cell_value(row_no, 2)  # 2列是问题类型

    if result != None:
        #         p("result=" + result)
        wsheet.write(i, 5, result, style)  # 5列是自动关联结果

    
def p(s):
    if 1:
        print(s)


if __name__ == '__main__':

    # 1.apr excel
    #     p(str(len_apr_list))
    for i in range(len_apr_list):
        #         p("i=" + str(i))
        try:
            rexcel_apr[i] = xlrd.open_workbook(apr_list[i][1], formatting_info=True)
            rsheet1_apr[i] = rexcel_apr[i].sheet_by_index(0)
        except:
            print("error: open_workbook " + apr_list[i][1] + "")
            
    # 2.beta excel
    rexcel_beta = xlrd.open_workbook(path_beta, formatting_info=True)
    rsheet1 = rexcel_beta.sheet_by_index(0)

    wexcel = copy(rexcel_beta)
    wsheet1 = wexcel.get_sheet(0)

    # 3.begin    
    row_count = rsheet1.nrows
    #     p("row_count=" + str(row_count))
    for i in range(1, row_count):
        
        version = rsheet1.cell_value(i, 1)  # 1列是version
        
        index = get_version_index(version)
        if index == -1:
            print("error: get_version_index " + i)
            continue
        
        sn = rsheet1.cell_value(i, 2)  # 2列是SN
        time = rsheet1.cell_value(i, 3)  # 3列是time
        beta_time = datetime.datetime.strptime(time, "%Y-%m-%d %H:%M:%S")
    
        if rexcel_apr[index] != None:
            search_by_sn_time(rsheet1_apr[index], sn, beta_time, wsheet1, i)
    
    # 4.end
    wsheet1.col(5).width = 256 * 40  
    wexcel.save(path_beta)
    print("over")
